using Dapper;
using Finance.Api.Read.Applications.Commands.台账管理;
using Finance.Api.Read.Dtos;
using Finance.Domain;
using Finance.ErrorCode;
using Finance.Infrastructure;
using MediatR;
using System.Text;
using System.Linq;

namespace Finance.Api.Read.Applications.CommandHands.台账管理
{
    public class InventoryFlowQueryCommandHandlers : IRequestHandler<InventoryFlowQueryCommand, ApiResult<ApiPaging<List<InventoryFlowDto>>>>
    {
        private readonly IDapperDb _db;
        private readonly ILogger<InventoryFlowQueryCommandHandlers> _logger;

        public InventoryFlowQueryCommandHandlers(IDapperDb db, ILogger<InventoryFlowQueryCommandHandlers> logger)
        {
            _db = db;
            _logger = logger;
        }

        public async Task<ApiResult<ApiPaging<List<InventoryFlowDto>>>> Handle(InventoryFlowQueryCommand request, CancellationToken cancellationToken)
        {
            var result = new ApiResult<ApiPaging<List<InventoryFlowDto>>>();
            StringBuilder stringBuilder = new StringBuilder();
            StringBuilder totalNum = new StringBuilder();

            ApiPaging<List<InventoryFlowDto>> res = new ApiPaging<List<InventoryFlowDto>>();
            var parameters = new DynamicParameters();
            try
            {
                _logger.LogInformation("开始查询出入库记录，参数：ProductNo={ProductNo}, OutInNo={OutInNo}, LocationName={LocationName}, OutInType={OutInType}", 
                    request.ProductNo, request.OutInNo, request.LocationName, request.OutInType?.ToString());

                // 先检查是否有数据
                var testQuery = "SELECT COUNT(1) FROM OutIn WHERE IsDelete = 0";
                var totalRecords = await _db.QueryFirst<int>(testQuery);
                _logger.LogInformation("OutIn表中总记录数：{TotalRecords}", totalRecords.ToString());

                if (!string.IsNullOrEmpty(request.ProductNo))
                {
                    var productQuery = "SELECT COUNT(1) FROM OutIn WHERE IsDelete = 0 AND ProductNo = @ProductNo";
                    var productCount = await _db.QueryFirst<int>(productQuery, new { ProductNo = request.ProductNo });
                    _logger.LogInformation("商品编号 {ProductNo} 的记录数：{ProductCount}", request.ProductNo, productCount.ToString());
                }

                stringBuilder.Append(@"SELECT 
                            a.Id,
                            a.OutInNo,
                            a.OutInTime,
                            a.OutInType,
                            CASE 
                                WHEN a.OutInType = 1 THEN '出库'
                                WHEN a.OutInType = 2 THEN '入库'
                                ELSE '未知'
                            END AS OutInTypeName,
                            a.LocationName,
                            a.GoinNumber,
                            a.OutNumber,
                            a.ProductNo,
                            a.ProductName
                    FROM OutIn a
                    WHERE 1=1
                            AND a.IsDelete=0 ");

                // 商品编号查询条件
                if (!string.IsNullOrEmpty(request.ProductNo))
                {
                    stringBuilder.Append(" AND a.ProductNo = @ProductNo ");
                    parameters.Add("ProductNo", request.ProductNo);
                    _logger.LogInformation("添加商品编号查询条件：{ProductNo}", request.ProductNo);
                }

                // 出入库单号查询条件
                if (!string.IsNullOrEmpty(request.OutInNo))
                {
                    stringBuilder.Append(" AND a.OutInNo LIKE @OutInNo ");
                    parameters.Add("OutInNo", $"%{request.OutInNo}%");
                    _logger.LogInformation("添加出入库单号查询条件：{OutInNo}", request.OutInNo);
                }

                // 仓库名称查询条件
                if (!string.IsNullOrEmpty(request.LocationName))
                {
                    stringBuilder.Append(" AND a.LocationName LIKE @LocationName ");
                    parameters.Add("LocationName", $"%{request.LocationName}%");
                    _logger.LogInformation("添加仓库名称查询条件：{LocationName}", request.LocationName);
                }

                // 出入库类型查询条件
                if (request.OutInType.HasValue)
                {
                    stringBuilder.Append(" AND a.OutInType = @OutInType ");
                    parameters.Add("OutInType", request.OutInType.Value);
                    _logger.LogInformation("添加出入库类型查询条件：{OutInType}", request.OutInType.Value.ToString());
                }

                // 出入库日期开始查询条件
                if (request.OutInTimeStart.HasValue)
                {
                    stringBuilder.Append(" AND a.OutInTime >= @OutInTimeStart ");
                    parameters.Add("OutInTimeStart", request.OutInTimeStart.Value);
                    _logger.LogInformation("添加出入库日期开始查询条件：{OutInTimeStart}", request.OutInTimeStart.Value.ToString());
                }

                // 出入库日期结束查询条件
                if (request.OutInTimeEnd.HasValue)
                {
                    stringBuilder.Append(" AND a.OutInTime <= @OutInTimeEnd ");
                    parameters.Add("OutInTimeEnd", request.OutInTimeEnd.Value);
                    _logger.LogInformation("添加出入库日期结束查询条件：{OutInTimeEnd}", request.OutInTimeEnd.Value.ToString());
                }

                // 构建总数查询SQL
                totalNum.Append(@"SELECT COUNT(1)
                            FROM OutIn a
                            WHERE 1=1
                                    AND a.IsDelete=0 ");

                // 为总数查询添加相同的查询条件
                if (!string.IsNullOrEmpty(request.ProductNo))
                {
                    totalNum.Append(" AND a.ProductNo = @ProductNo ");
                }

                if (!string.IsNullOrEmpty(request.OutInNo))
                {
                    totalNum.Append(" AND a.OutInNo LIKE @OutInNo ");
                }

                if (!string.IsNullOrEmpty(request.LocationName))
                {
                    totalNum.Append(" AND a.LocationName LIKE @LocationName ");
                }

                if (request.OutInType.HasValue)
                {
                    totalNum.Append(" AND a.OutInType = @OutInType ");
                }

                if (request.OutInTimeStart.HasValue)
                {
                    totalNum.Append(" AND a.OutInTime >= @OutInTimeStart ");
                }

                if (request.OutInTimeEnd.HasValue)
                {
                    totalNum.Append(" AND a.OutInTime <= @OutInTimeEnd ");
                }

                // 获取总数
                int totalCount = await _db.QueryFirst<int>(totalNum.ToString(), parameters);
                _logger.LogInformation("查询总数成功，总数：{TotalCount}", totalCount.ToString());

                // 添加分页和排序
                stringBuilder.Append($" ORDER BY a.OutInTime DESC LIMIT {((request.index - 1) * request.size)}, {request.size}");
                _logger.LogInformation("添加分页和排序条件，页码：{Index}, 每页数量：{Size}", request.index.ToString(), request.size.ToString());

                // 执行数据查询
                var list = await _db.Query<InventoryFlowDto>(stringBuilder.ToString(), parameters);
              
                
                // 手动设置序号
                int rowNumber = (request.index - 1) * request.size + 1;
                var resultList = new List<InventoryFlowDto>();
                foreach (var item in list)
                {
                    item.RowNum = rowNumber++;
                    resultList.Add(item);
                }
                
                res.data = resultList;
                res.totalcount = totalCount;
                res.totalpcount = (int)Math.Ceiling(totalCount * 1.0 / request.size);
                result.data = res;
                result.message = "出入库记录查询成功";
                result.Code = Enumcode.ok;
                _logger.LogInformation("出入库记录查询成功");
            }
            catch (Exception ex)
            {
                _logger.LogError("出入库记录查询失败: {Message}", ex.Message);
                result.message = "出入库记录查询失败";
                result.Code = Enumcode.fail;
                throw;
            }

            return await Task.FromResult(result);
        }
    }
}
